package com.ysb.searchserver;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

import com.ysb.util.Pair;

public class DBUtil {

	private static DataSource dataSource;

	static {
		dataSource = (DataSource) SpringContextUtil.getBean("dataSource");
	}

	public static Connection getConnection() throws Throwable {
		return dataSource.getConnection();
	}

	public static void closeConnection(Connection conn) {
		try {
			conn.close();
		} catch (Throwable th) {
			BizLogger.error(th, "DBUtil.closeConnection");
		}

	}

	public static int update(String sql) throws Throwable {
		Connection con = null;
		Statement statement = null;
		try {
			con = DBUtil.getConnection();
			statement = con.createStatement();
			return statement.executeUpdate(sql);
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (Throwable t) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Throwable t) {
				}
			}
		}
	}

	public static Pair<CachedRowSet, Integer> selectPage(String sql,
			int pageNo, int pageSize) throws Throwable {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		pageNo = pageNo > 0 ? pageNo : 1;
		try {
			conn = DBUtil.getConnection();
			int oBegin, oEnd;
			String regEx = ".*\\s+limit\\s+(\\d+)\\s*,?\\s*(\\d*).*";
			Pattern pt = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE);
			Matcher mc = pt.matcher(sql);
			boolean isContainLimit = false;
			if (mc.find()) {
				isContainLimit = true;
			}
			if (isContainLimit) {
				if (!mc.group(2).equals("")) {
					oBegin = Integer.parseInt(mc.group(1));
					oEnd = oBegin + Integer.parseInt(mc.group(2));
				} else {
					oBegin = 0;
					oEnd = Integer.parseInt(mc.group(1));
				}
			} else {
				oBegin = 0;
				oEnd = Integer.MAX_VALUE;
			}
			int begin = (oBegin + (pageNo - 1) * pageSize) > oEnd ? oEnd
					: (oBegin + (pageNo - 1) * pageSize);
			int distinct = oEnd - (oBegin + pageNo * pageSize);
			int offset = (distinct >= 0 ? pageSize : (oEnd - begin));

			regEx = "^\\s*select\\s+";
			pt = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE);
			mc = pt.matcher(sql);
			sql = mc.replaceFirst("select SQL_CALC_FOUND_ROWS ");

			if (isContainLimit) {
				regEx = "(.*)\\s+limit\\s+(\\d+)\\s*,?\\s*(\\d*)$";
				pt = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE);
				mc = pt.matcher(sql);
				sql = mc.replaceAll("$1 limit " + begin + "," + offset);
			} else {
				sql = sql + " limit " + begin + "," + offset;
			}
			if (offset > 0) {// 如果offset=0,就不查数据了
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				RowSetFactory rowSetFactory = RowSetProvider.newFactory();
				CachedRowSet record = rowSetFactory.createCachedRowSet();
				record.populate(rs);
				rs = stmt.executeQuery("select found_rows() as ct");
				int totalRecord = 0;
				if (rs.next()) {
					totalRecord = rs.getInt("ct");
				}
				return new Pair<CachedRowSet, Integer>(record, totalRecord);
			}
		} catch (SQLException e) {
			throw new SQLException("An error occur when queryPage.Error:"
					+ e.toString());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} finally {
				if (conn != null)
					conn.close();
			}
		}
		return new Pair<CachedRowSet, Integer>(null, 0);
	}

	public static CachedRowSet select(String sql) throws Throwable {
		Connection con = null;
		Statement statement = null;
		ResultSet rs = null;
		try {
			con = DBUtil.getConnection();
			statement = con.createStatement();
			rs = statement.executeQuery(sql);
			// JDK 7以上支持
			RowSetFactory rowSetFactory = RowSetProvider.newFactory();
			CachedRowSet crs = rowSetFactory.createCachedRowSet();
			crs.populate(rs);
			return crs;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Throwable t) {
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (Throwable t) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Throwable t) {
				}
			}
		}
	}

}
